<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="statements.xsd">
    <module name="messages" >
        <table name="message" description="通知消息表" >
            <column name="message_id" type="string" primaryKey="yes" defaultValue="" />
            <column name="title" type="string" size="200" unique="yes" defaultValue="" />
            <column name="receiver_name" type="string" description="Name of reciever" />
            <column name="sender_name" type="string" />
            <column name="send_date" type="datetime" />
            <column name="content" type="string" />
            <column name="status" type="int" />
            <column name="has_read" type="int" defaultValue="0" />
        </table>
        <statement name="find" allowPaging="true" resultType="table"
            resultTable="message" >
            <![CDATA[
            select
            msg.message_id messageId,
            msg.title,
            user1.`name` as receiverName,
            user2.`name` as senderName,
            msg.send_date as sendDate,
            msg.`status`,
            ifnull(mu.has_read , 0) as hasRead
            from
            msg_messages msg
            left join   sec_user user1 ON msg.receiver_id = user1.user_id
            left join   sec_user user2 ON msg.sender_id = user2.user_id
            left join   msg_user mu ON msg.message_id = mu.message_id
            left join   sec_user user3 ON user3.user_id = mu.user_id
            where    msg.`status` = 1
            and (user3.`name` = #receiver_name# or mu.`user_id` is null)
                ]]>
            <dynamic prepend="and">
                <isNotEmpty parameter="sender_name">
                    <![CDATA[
                    user2.`name` = #sender_name#
                    ]]>
                </isNotEmpty>
                <isNotEmpty parameter="has_read">
                    <![CDATA[
                    ifnull(mu.has_read , 0) = #has_read#
                    ]]>
                </isNotEmpty>
            </dynamic>
            <![CDATA[order by has_read, send_date desc]]>
            <parameter name="receiver_name" type="string" />
            <parameter name="sender_name" type="string" />
            <parameter name="has_read" type="boolean" />
        </statement>

        <statement name="find_count" resultType="int">
            <![CDATA[
            select count(*)
            from
            msg_messages msg
            left join
            sec_user user1 ON msg.receiver_id = user1.user_id
            left join
            sec_user user2 ON msg.sender_id = user2.user_id
            left join
            msg_user mu ON msg.message_id = mu.message_id
            left join
            sec_user user3 ON user3.user_id = mu.user_id
            where
            msg.`status` = 1
            and (user3.`name` = #receiver_name# or mu.`user_id` is null)
            ]]>
            <dynamic prepend="and">
                <isNotEmpty parameter="sender_name">
                    <![CDATA[
                    user2.`name` = #sender_name#
                    ]]>
                </isNotEmpty>
                <isNotEmpty parameter="has_read">
                    <![CDATA[
                    ifnull(mu.has_read , 0) = #has_read#
                    ]]>
                </isNotEmpty>
            </dynamic>
            <parameter name="receiver_name" type="string" />
            <parameter name="sender_name" type="string" />
            <parameter name="has_read" type="boolean" />
        </statement>

        <statement name="find_messages" allowPaging="true" resultType="table">
            <![CDATA[
            select
            msg.message_id messageId,
            msg.`title`,            
            msg.send_date as sendDate,
            msg.`status`,
            msg.content
            from
            msg_messages msg
            ]]>
            <dynamic prepend="where">
                <isNotEmpty parameter="title">
                    <![CDATA[
                    `title` like concat('%', #title# , '%')
                    ]]>
                </isNotEmpty>
                <isNotEmpty parameter="content">
                    <![CDATA[
                    `content` like concat('%', #content# , '%')
                    ]]>
                </isNotEmpty>
            </dynamic>
            <![CDATA[order by send_date desc]]>
            <parameter name="title" type="string" matchType="contains"/>
            <parameter name="content" type="string" matchType="contains"/>            
        </statement>

        <statement name="find_messages_count"  resultType="int">
            <![CDATA[
            select
            count(*)        
            from
            msg_messages msg
            ]]>
            <dynamic prepend="where">
                <isNotEmpty parameter="title">
                    <![CDATA[
                    `title` like concat('%', #title# , '%')
                    ]]>
                </isNotEmpty>
                <isNotEmpty parameter="content">
                    <![CDATA[
                    `content` like concat('%', #content# , '%')
                    ]]>
                </isNotEmpty>
            </dynamic>        
            <parameter name="title" type="string" matchType="contains"/>
            <parameter name="content" type="string" matchType="contains"/>
        </statement>

        <statement name="find_by_id" resultType="table" resultTable="message" >
            <![CDATA[
            select
            msg.message_id messageId,
            msg.title,
            user1.`name` as receiverName,
            user2.`name` as senderName,
            msg.send_date sendDate,
            msg.content,
            msg.`status`
            from
            msg_messages msg
            left join
            sec_user user1 ON msg.receiver_id = user1.user_id
            left join
            sec_user user2 ON msg.sender_id = user2.user_id
            where
            msg.message_id = #message_id#
            ]]>
            <parameter name="message_id" type="string" />
        </statement>

        <statement name="mark_has_read" commandType="sp" >
            <![CDATA[mark_message_has_read]]>
            <parameter name="message_id" type="string" />
            <parameter name="receiver_name" type="string" />
        </statement>

        <statement name="update" >
            <![CDATA[
            update msg_messages
            set 
            title = #title#,
            receiver_id = #receiver_id#,
            sender_id = #sender_id#,
            send_date = #send_date#,
            content = #content#
            where message_id = #message_id#
            ]]>
            <parameter name="message_id" type="string" />
            <parameter name="title" type="string"/>
            <parameter name="receiver_id" type="string"/>
            <parameter name="sender_id" type="string"/>
            <parameter name="send_date" type="datetime"/>
            <parameter name="content" type="string"/>
        </statement>

        <statement name="insert" >
            <![CDATA[
            insert into msg_messages
            (message_id, 
            title, 
            receiver_id,
            sender_id, 
            send_date, 
            content, 
            `status`)
            values(
            #message_id#, 
            #title#, 
            #receiver_id#, 
            #sender_id#, 
            #send_date#, 
            #content#, 
            0);
            ]]>
            <parameter name="message_id" type="string"/>
            <parameter name="title" type="string"/>
            <parameter name="receiver_id" type="string"/>
            <parameter name="sender_id" type="string"/>
            <parameter name="send_date" type="datetime"/>
            <parameter name="content" type="string"/>
        </statement>

        <statement name="send" >
            <![CDATA[
            update msg_messages set `status` = 1 where message_id = #message_id#;
            insert into msg_user(message_id,user_id,has_read)
            select #message_id#,user_id,0
            from sec_user where `status`=1;
            ]]>
            <parameter name="message_id" type="string"/>
        </statement>

        <statement name="delete" >
            <![CDATA[
            delete from msg_user where message_id = #message_id#;    
            delete from msg_messages where message_id = #message_id#;            
            ]]>
            <parameter name="message_id" type="string"/>
        </statement>

        <statement name="user_init" >
            <![CDATA[            
            insert into msg_user(message_id,user_id,has_read)
            select message_id,#user_id#,0
            from msg_messages where `status`=1;
            ]]>
            <parameter name="user_id" type="string"/>
        </statement>
    </module>
</root>
